# @Time:   2020/12/8 14:58
# @Author: Zong Wei
import json
from time import strftime

import pymysql.cursors
import pymssql


class DB(object):
    @staticmethod
    def sqlserver_operate(database_name="MCPERP_TEST",
                          sql="SELECT * FROM [dbo].[FMS_Trucking] WHERE Customer_Code = '201808018582' ORDER BY UpdateDT DESC"):
        # sql服务器名-数据库IP
        server_name = '10.2.18.202'
        # 登陆用户名和密码
        username = 'sa'
        password = 'Sql0077'
        # 建立连接并获取cursor
        conn = pymssql.connect(server_name, username, password, database_name, charset='GBK', as_dict=True)
        cursor = conn.cursor()
        cursor.execute(sql)

        # # 如果连接时没有设置autocommit为True的话，必须主动调用commit() 来保存更改。
        # conn.commit()

        # 获取数据库记录
        # row = cursor.fetchall()
        # num = 1
        # for i in row:
        #     i['CreateDT'] = i['CreateDT'].strftime('%Y-%m-%d %H:%M:%S')
        #     i['UpdateDT'] = i['UpdateDT'].strftime('%Y-%m-%d %H:%M:%S')
        #     print(i)
        #     num += 1
        #     if num > 2:
        #         break

        row = cursor.fetchone()
        print(row)
        print(row['RowVer'].decode('hex'))
        print(row['RowVer'])

        # row = cursor.fetchall()
        # # 循环打印记录(这里只有一条，所以只打印出一条)
        # while row:
        #     print(row[0].encode('latin-1').decode('gbk'))
        #     row = cursor.fetchone()

        conn.close()

    # @staticmethod
    # def postgresql_operate(database_name='slp_system', sql=None):
    #     db = postgresql.open(POSTGRESQL_INFO + database_name)
    #     ps = db.prepare(sql)
    #     return db, ps
    #
    # @staticmethod
    # def mongodb_operate(database_name='slp_order', collections='application'):
    #     mongo_client = MongoClient(MONGODB_INFO, MONGODB_PORT)
    #     db = mongo_client[database_name]  # Add database
    #     collection = db[collections]  # Select table
    #     return mongo_client, collection

    # @staticmethod
    # def mysql_operate(sql='SELECT `id`, `password` FROM `users` WHERE `email`=%s"', param='xxx'):
    #     # Connect to the database
    #     connection = pymysql.connect(host='localhost',
    #                                  user='user',
    #                                  password='passwd',
    #                                  db='db',
    #                                  charset='utf8mb4',
    #                                  cursorclass=pymysql.cursors.DictCursor)
    #
    #     try:
    #         with connection.cursor() as cursor:
    #             # Read a single record
    #             # sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
    #             cursor.execute(sql, (param,))
    #             result = cursor.fetchone()   # 单条记录
    #             # result = cursor.fetchall()   # 多条记录
    #             print(result)
    #     finally:
    #         connection.close()


if __name__ == '__main__':
    DB.sqlserver_operate()
